using System;
using System.Data;
using System.Data.Odbc;
using System.Configuration;
using System.Collections.Generic;
using System.Runtime.Serialization;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.ServiceModel;

namespace Reviewer.Core
{
    /// <summary>
    /// Summary description for GamesService
    /// </summary>

    [ServiceContract]
    public class GamesService : IDisposable
    {
        [OperationContract]
        public List<Entities.Game> GetListOfGamesWithoutScreenshots()
        {
            return new Data.GameData().GetGamesWithoutScreenshot();
        }
        [OperationContract]
        public Entities.Game Save(Entities.Game game)
        {
            return new Data.GameData().Save(game);
        }
        [OperationContract]
        public Entities.Game GetById(Int32 id)
        {
            return new Data.GameData().GetById(id);
        }

        public void Dispose() { }
    }

}
namespace Reviewer.Core.Data
{
    public class GameData : BaseEntityData<Entities.Game>
    {
        public Entities.Game Save(Entities.Game game)
        {
            int gameId = -1;
            if (game.Id < 0) // new item
            {
                OdbcCommand command = this.GetCommand(
                    String.Format("INSERT INTO V2_Spil VALUES(NULL, '{0}', '{1}', '{2}', '{3}', {4}, '{5}', '{6}', '{7}'); SELECT @@IDENTITY;",
                    game.SpilId.ToString("D"),
                    GetSafeString(game.Title),
                    GetSafeString(game.Publisher),
                    GetSafeString(game.Developer),
                    game.GameTypeId,
                    GetSafeString(game.MultiplayerInfo),
                    GetSafeString(game.GameFormat),
                    game.ReleaseDate.ToString("yyyy-MM-dd")));

                object itemId = this.ExecuteScalar(command);

                try
                {
                    gameId = (int)itemId;
                }
                catch
                {
                    throw new Exception("Failed to get object identity");
                }
            }
            else
            {
                gameId = game.Id;

                OdbcCommand command = this.GetCommand(
                    String.Format("UPDATE INTO V2_Spil SET SpilId='{0}', Titel='{1}', Udgiver='{2}', Udvikler='{3}', Genre={4}, Multi='{5}', Format='{6}', ReleaseDato'{7}' WHERE Id = {8}",
                    game.SpilId.ToString("D"),
                    GetSafeString(game.Title),
                    GetSafeString(game.Publisher),
                    GetSafeString(game.Developer),
                    game.GameTypeId,
                    GetSafeString(game.MultiplayerInfo),
                    GetSafeString(game.GameFormat),
                    game.ReleaseDate.ToString("yyyy-MM-dd"),
                    game.Id));
                this.ExecuteNonQuery(command);
            }

            return GetById(gameId);
        }
        public Entities.Game GetById(int id)
        {
            OdbcCommand command = this.GetCommand(String.Format("SELECT * FROM V2_Spil WHERE Id = {0}", id));
            return this.GetItem(command);
        }
        public List<Entities.Game> GetGamesReleasedAfter(DateTime date)
        {
            OdbcCommand command = this.GetCommand(String.Format("SELECT * FROM V2_Spil WHERE ReleaseDate > '{0}'", date.ToString("yyyy-MM-dd")));
            return this.GetItems(command);
        }
        public List<Entities.Game> GetGamesWithoutScreenshot()
        {
            OdbcCommand command = this.GetCommand("SELECT S.*, SC.* FROM V2_Spil S LEFT OUTER JOIN V2_Screenshots SC ON S.SpilId = SC.SpilId WHERE SC.Id IS NULL");
            return this.GetItems(command);
        }

        public override Reviewer.Core.Entities.Game ReadFrom(IDataReader reader)
        {
            return Entities.Game.Create(
                reader.GetInt32(reader.GetOrdinal("Id")),
                new Guid(reader.GetString(reader.GetOrdinal("SpilId"))),
                reader.GetString(reader.GetOrdinal("Titel")),
                reader.GetString(reader.GetOrdinal("Udgiver")),
                reader.GetString(reader.GetOrdinal("Udvikler")),
                reader.GetInt32(reader.GetOrdinal("Genre")),
                reader.GetString(reader.GetOrdinal("Multi")),
                reader.GetString(reader.GetOrdinal("Format")),
                reader.IsDBNull(reader.GetOrdinal("ReleaseDato")) ? DateTime.MinValue : Convert.ToDateTime(reader.GetValue(reader.GetOrdinal("ReleaseDato"))));
        }
    }

    public abstract class BaseEntityData<T> where T : class
    {
        public OdbcCommand GetCommand(string commandName)
        {
            OdbcCommand command = new OdbcCommand();
            command.Connection = new OdbcConnection(new ReviewerMySql().ConnStr);
            command.CommandType = CommandType.Text;
            command.CommandText = commandName;

            return command;
        }

        public T GetItem(OdbcCommand command)
        {
            using (OdbcDataReader reader = this.ExecuteReader(command))
            {
                if (reader.Read())
                {
                    return ReadFrom(reader);
                }
            }
            return null;
        }

        public List<T> GetItems(OdbcCommand command)
        {
            List<T> items = new List<T>();
            using (OdbcDataReader reader = this.ExecuteReader(command))
            {
                while (reader.Read())
                {
                    T item = ReadFrom(reader);
                    if (null != item)
                    {
                        items.Add(item);
                    }
                }
            }
            return items;
        }

        public OdbcDataReader ExecuteReader(OdbcCommand command)
        {
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                    command.Connection.Open();

                return command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                throw;
            }
        }

        public object ExecuteScalar(OdbcCommand command)
        {
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                    command.Connection.Open();
                return command.ExecuteScalar();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }

        public int ExecuteNonQuery(OdbcCommand command)
        {
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                    command.Connection.Open();
                return command.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }

        public string GetSafeString(string input)
        {
            return input.Replace("'", "''");
        }

        public abstract T ReadFrom(IDataReader reader);
    }
}
namespace Reviewer.Core.Entities
{
    [Serializable]
    [DataContract]
    public class Game
    {
        public static Game Create(Guid spilId, string title, string publisher, string developer, int gameTypeId, string multiPlayerInfo, string gameFormat, DateTime releaseDate)
        {
            Game entity = new Game();
            entity.m_Id = -1;
            entity.m_SpilId = spilId;
            entity.m_Title = title;
            entity.m_Publisher = publisher;
            entity.m_Developer = developer;
            entity.m_GameTypeId = gameTypeId;
            entity.m_MultiplayerInfo = multiPlayerInfo;
            entity.m_GameFormat = gameFormat;
            entity.m_ReleaseDate = releaseDate;
            return entity;
        }
        public static Game Create(int id, Guid spilId, string title, string publisher, string developer, int gameTypeId, string multiPlayerInfo, string gameFormat, DateTime releaseDate)
        {
            Game entity = new Game();
            entity.m_Id = id;
            entity.m_SpilId = spilId;
            entity.m_Title = title;
            entity.m_Publisher = publisher;
            entity.m_Developer = developer;
            entity.m_GameTypeId = gameTypeId;
            entity.m_MultiplayerInfo = multiPlayerInfo;
            entity.m_GameFormat = gameFormat;
            entity.m_ReleaseDate = releaseDate;
            return entity;
        }

        private Game() { }

        private int m_Id;
        /// <summary>
        /// Gets the id.
        /// </summary>
        /// <value>The id.</value>
        [DataMember]
        public int Id
        {
            get { return m_Id; }
        }

        private Guid m_SpilId;
        /// <summary>
        /// Gets or sets the spil id.
        /// </summary>
        /// <value>The spil id.</value>
        [DataMember]
        public Guid SpilId
        {
            get { return m_SpilId; }
            set { m_SpilId = value; }
        }

        private String m_Title;
        /// <summary>
        /// Gets or sets the title.
        /// </summary>
        /// <value>The title.</value>
        [DataMember]
        public String Title
        {
            get { return m_Title; }
            set { m_Title = value; }
        }

        private string m_Publisher;
        /// <summary>
        /// Gets or sets the publisher.
        /// </summary>
        /// <value>The publisher.</value>
        [DataMember]
        public string Publisher
        {
            get { return m_Publisher; }
            set { m_Publisher = value; }
        }

        private string m_Developer;
        /// <summary>
        /// Gets or sets the developer.
        /// </summary>
        /// <value>The developer.</value>
        [DataMember]
        public string Developer
        {
            get { return m_Developer; }
            set { m_Developer = value; }
        }

        private int m_GameTypeId;
        /// <summary>
        /// Gets or sets the game type id.
        /// </summary>
        /// <value>The game type id.</value>
        [DataMember]
        public int GameTypeId
        {
            get { return m_GameTypeId; }
            set { m_GameTypeId = value; }
        }

        private string m_MultiplayerInfo;
        /// <summary>
        /// Gets or sets the multiplayer info.
        /// </summary>
        /// <value>The multiplayer info.</value>
        [DataMember]
        public string MultiplayerInfo
        {
            get { return m_MultiplayerInfo; }
            set { m_MultiplayerInfo = value; }
        }

        private string m_GameFormat;
        /// <summary>
        /// Gets or sets the game format.
        /// </summary>
        /// <value>The game format.</value>
        [DataMember]
        public string GameFormat
        {
            get { return m_GameFormat; }
            set { m_GameFormat = value; }
        }

        private DateTime m_ReleaseDate;
        /// <summary>
        /// Gets or sets the release date.
        /// </summary>
        /// <value>The release date.</value>
        [DataMember]
        public DateTime ReleaseDate
        {
            get { return m_ReleaseDate; }
            set { m_ReleaseDate = value; }
        }

        public string UrlFormatedTitle
        {
            get { return System.Web.HttpUtility.UrlEncode(Title); }
        }
    }
}